package com.qiche.util;

import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.net.HttpURLConnection;
import java.net.URL;
import java.net.URLConnection;
import java.util.ArrayList;
import java.util.List;

import com.qiche.config.YmlConfig;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;

@Component
public class ExcelUtil {
	
    @Autowired
    private YmlConfig ymlConfig;


	/** 
	   * 解析Excel里面的数据 
	   * @param path
	   * @return 
	   */  
	public List<List<String>>  readXls(String path) throws Exception {
        // HSSFWorkbook 标识整个excel
	    Workbook wb = null;
	    String fileName=path.substring(path.lastIndexOf("/")+1);
	    //因为修改成FastDfs上传文件，所以要读网络文件进行解析
        URL url=new URL(ymlConfig.getFastdfsDomainUrl()+path);
        URLConnection urlConnection=url.openConnection();
        urlConnection.connect();
        HttpURLConnection httpURLConnection= (HttpURLConnection) urlConnection;
       int resultCode= httpURLConnection.getResponseCode();
       if(resultCode!=HttpURLConnection.HTTP_OK){
           System.out.println("远程网络文件连接失败");
           return  null;
       }else{
           urlConnection.getInputStream();
       }
	  //  File file=new File(fastdfsDomainUrl+path);

        InputStream is=new BufferedInputStream(urlConnection.getInputStream());
	   // InputStream is = new FileInputStream(file);
        //根据文件名判断文件是2003版本还是2007版本  
        if(isExcel2007(fileName)){  
           wb = new XSSFWorkbook(is);   
        }else{  
           wb = new HSSFWorkbook(is);   
        }  
		
       
        List<List<String>> result = new ArrayList<List<String>>();
        int size = wb.getNumberOfSheets();
        // 循环每一页，并处理当前循环页
        for (int numSheet = 0; numSheet < size; numSheet++) {
            // HSSFSheet 标识某一页
            Sheet sheet = wb.getSheetAt(numSheet);
            if (sheet == null) {
                continue;
            }
            // 处理当前页，循环读取每一行
            for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
                // HSSFRow表示行
                Row row = sheet.getRow(rowNum);
                if(row == null)
                    continue;
                int minColIx = row.getFirstCellNum();
                int maxColIx = row.getLastCellNum();
                List<String> rowList = new ArrayList<String>();
                // 遍历改行，获取处理每个cell元素
                for (int colIx = minColIx; colIx < maxColIx; colIx++) {
                    // HSSFCell 表示单元格
                    Cell cell = row.getCell(colIx);
                    if (cell == null) {
                        continue;
                    }
                    rowList.add(getStringVal(cell));
                }
                result.add(rowList);
            }
        }
        return result;
    }
	

   	public  String getStringVal(Cell cell) {
           switch (cell.getCellType()) {
           case Cell.CELL_TYPE_BOOLEAN:
               return cell.getBooleanCellValue() ? "TRUE" : "FALSE";
           case Cell.CELL_TYPE_FORMULA:
               return cell.getCellFormula();
           case Cell.CELL_TYPE_NUMERIC:
               cell.setCellType(Cell.CELL_TYPE_STRING);
               return cell.getStringCellValue();
           case Cell.CELL_TYPE_STRING:
               return cell.getStringCellValue();
           default:
               return "";
           }
       }
	  
	    // @描述：是否是2003的excel，返回true是2003   
	    public  boolean isExcel2003(String filePath)  {    
	        return filePath.matches("^.+\\.(?i)(xls)$");    
	    }    
	     
	    //@描述：是否是2007的excel，返回true是2007   
	    public  boolean isExcel2007(String filePath)  {    
	        return filePath.matches("^.+\\.(?i)(xlsx)$");    
	    }    
	      
	  /** 
	   * 验证EXCEL文件 
	   * @param filePath 
	   * @return 
	   */  
	  public  boolean validateExcel(String filePath){  
	        if (filePath == null || !(isExcel2003(filePath) || isExcel2007(filePath))){    
	            return false;    
	        }    
	        return true;  
	  } 
}
